package com.whzykj.ctrl;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.whzykj.common.CommonUtils;
import com.whzykj.common.DateUtil;
import com.whzykj.common.DictCache;
import com.whzykj.model.Dictionary;
import com.whzykj.model.OrderInfo;
import com.whzykj.model.Userinfo;
import com.whzykj.service.OrderService;
import com.whzykj.service.impl.OrderServiceImpl;

public class OrderController extends Controller {

	private Object PoiRender;

	public void index() {
		this.renderJsp("/admin/system/orderlist.jsp");
	}

	/*
	 * 列表显示所有客户信息
	 */
	public void orderlist() {
		Integer page = this.getParaToInt("page");
		Integer rows = this.getParaToInt("rows");

		Integer station = this.getParaToInt("station");

		String starttime = this.getPara("start_time");
		String endtime = this.getPara("end_time");
		String orderstaus = this.getPara("orderstaus");

		String sqlBefore = " SELECT o.*,c.CUST_NAME,C.MOBILE,sta.STATION_NAME,(select SUM(sale_num) from orderdetails where order_id=o.order_id) as SALE_NUM,c.POSITION   ";
		StringBuffer sqlAfter = new StringBuffer(
				" from orderinfo o inner JOIN customer c ON o.CUST_ID=c.CUST_ID ");
		sqlAfter.append(" INNER JOIN station sta on sta.STATION_ID=c.TAKE_STATION_ID where 1=1 ");
		if (station != null && station != -1) {
			sqlAfter.append(" and c.station_id=" + station);
		}
		if (orderstaus != null && !"-1".equals(orderstaus)) {
			if (orderstaus.equals("0")) {
				sqlAfter.append(" and o.status<>'over' ");
			} else {
				sqlAfter.append(" and o.status='" + orderstaus + "' ");
			}
		}
		if (starttime != null && !"".equals(starttime) && endtime != null
				&& !"".equals(endtime)) {
			sqlAfter.append(" and o.order_date >= '" + starttime
					+ " 00:00:00' and o.order_date <= '" + endtime
					+ " 59:59:59' ");
		}
		sqlAfter.append(" ORDER BY order_date DESC  ");
		Page<OrderInfo> paginate = OrderInfo.me.paginate(page, rows, sqlBefore,
				sqlAfter.toString());
		List<OrderInfo> orderlist = paginate.getList();
		if (orderlist != null && orderlist.size() > 0) {
			for (OrderInfo orderInfo : orderlist) {
				Date orderdate = orderInfo.getDate("order_date");
				String status = orderInfo.getStr("status");
				String statusname = DictCache.cache.getDicByText(status);
				orderInfo.put("status_name", statusname);
				orderInfo.put("order_date", CommonUtils.getDateStr(orderdate));
			}
		}
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("rows", orderlist);
		map.put("total", paginate.getTotalRow());
		this.renderJson(map);
	}

	/***
	 * 订单状态变更
	 */
	public void changestatus() {
		String orderid = this.getPara("orderid");
		String status = this.getPara("status");
		OrderInfo order = OrderInfo.me.findById(orderid);
		order.set("status", status);
		boolean update = false;
		update = order.update();
		this.renderText(update ? "1" : "0");
	}

	/**
	 * 执行关闭订单的操作
	 */
	public void changeStatusToOver() {
		String orderid = this.getPara("orderid");
		int res = OrderInfo.me.closeOrder(orderid);
		this.renderText("" + res);
	}

	/**
	 * 根据条件查询所有的订单明细信息
	 */
	public void orderDetailsList() {
		try {
			String orderDate = this.getPara("orderDate");
			Integer stationId = this.getParaToInt("stationId");
			String orderStatus = this.getPara("orderStatus");
			String orderId = this.getPara("orderId");
			String custName = this.getPara("custName");
			String mobile = this.getPara("mobile");
			OrderService service = new OrderServiceImpl();
			int totals = service.getOderInfoListCount(orderDate, stationId,
					orderStatus, orderId, custName, mobile);
			int rowsPerPage = 10;
			int pages = (totals % rowsPerPage == 0) ? totals / rowsPerPage
					: totals / rowsPerPage + 1;
			Integer currentPage = this.getParaToInt("num");
			if (currentPage == null) {
				currentPage = 1;
			}
			int start = (currentPage - 1) * rowsPerPage;
			List list = service.getOderInfoList(orderDate, stationId,
					orderStatus, orderId, custName, mobile, start, rowsPerPage);
			this.setAttr("currentPage", currentPage);
			this.setAttr("pages", pages);
			this.setAttr("orderDate", orderDate);
			this.setAttr("stationId", stationId);
			this.setAttr("orderStatus", orderStatus);
			this.setAttr("orderId", orderId);
			this.setAttr("custName", custName);
			this.setAttr("mobile", mobile);
			this.setAttr("data", list);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.renderJsp("/admin/system/orderManage.jsp");
	}

	/**
	 * 查询订单信息
	 */
	public void findOrderDetails() {
		try {
			String orderId = this.getPara(0);
			String orderDate = this.getPara(1);
			if (orderDate != null && !orderDate.equals("")) {
				orderDate = orderDate.replaceAll("@", "-");
			}
			OrderService service = new OrderServiceImpl();
			int totals = service.getOderInfoListCount(orderDate, null, null,
					orderId, null, null);
			int rowsPerPage = 10;
			int pages = (totals % rowsPerPage == 0) ? totals / rowsPerPage
					: totals / rowsPerPage + 1;
			Integer currentPage = this.getParaToInt("num");
			if (currentPage == null) {
				currentPage = 1;
			}
			int start = (currentPage - 1) * rowsPerPage;
			List list = service.getOderInfoList(orderDate, null, null, orderId,
					null, null, start, rowsPerPage);
			this.setAttr("currentPage", currentPage);
			this.setAttr("pages", pages);
			this.setAttr("orderDate", orderDate);
			this.setAttr("stationId", -1);
			this.setAttr("orderStatus", -1);
			this.setAttr("orderId", orderId);
			this.setAttr("custName", "");
			this.setAttr("mobile", "");
			this.setAttr("data", list);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.renderJsp("/admin/system/orderManage.jsp");
	}

	/**
	 * 更新订单状态
	 */
	public void updateOrder() {
		int index = this.getParaToInt(0);
		String[] status = { "no_pay", "nosend", "no_pick", "finsh" }; // 这里使用下标为2的状态
		String s = status[index];
		String orderid = this.getPara(1);
		OrderInfo order = OrderInfo.me.findById(orderid);
		order.set("STATUS", s);
		order.update();
		orderDetailsList();
	}

	/**
	 * 查询订单状态列表
	 */
	public void findorderstatus() {
		String sql = "SELECT dic.DICT_CODE,dic.DICT_NAME from dictionary dic where dic.DICT_TYPE='STATUS'";
		List<Dictionary> dictList = Dictionary.me.find(sql);
		Dictionary dict = new Dictionary();
		dict.set("DICT_CODE", "-1").set("DICT_NAME", "全部");
		dictList.add(0, dict);
		this.renderJson(dictList);
	}

	/**
	 * 进入配送列表页面
	 */
	public void assignlistbefore() {
		this.setAttr("today", DateUtil.getToday());
		this.renderJsp("/admin/system/assignList.jsp");
	}

	/**
	 * 查询配送清单列表
	 */
	public void assignlist() {
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		String orderStatus = this.getPara("orderStatus");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> list=findAssignOrderList(startDate,endDate,orderStatus,stationid);
		List<Record> nList=findAssignNewOrderList(startDate,endDate,orderStatus,stationid);
		this.renderJson(list);
	}
	
	/**
	 * 进入配送列表页面(新的)
	 */
	public void assignnewlistbefore() {
		this.setAttr("today", DateUtil.getToday());
		this.renderJsp("/admin/system/assignnewlist.jsp");
	}
	
	/**
	 * 查询配送清单列表(新的)
	 */
	public void assignNewlist() {
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		String orderStatus = this.getPara("orderStatus");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> nList=findAssignNewOrderList(startDate,endDate,orderStatus,stationid);
		this.renderJson(nList);
	}
	
	/**
	 * 查询配送清单（老的）
	 * @param startDate
	 * @param endDate
	 * @param orderStatus
	 * @param stationid
	 * @return
	 */
	private List<Record> findAssignOrderList(String startDate,String endDate,String orderStatus,Integer stationid){
		if (startDate == null || startDate.equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.equals("")) {
			endDate = DateUtil.getToday();
		}
		if (orderStatus == null) {
			orderStatus = "-1";
		}
		
		if (stationid == null) {
			stationid = -1;
		}
		OrderService service = new OrderServiceImpl();
		List<Record> list = service.getAssignList(startDate, endDate,
				stationid, orderStatus);
		return list;
	}
	
	/**
	 * 查询配送清单（新的）
	 * @param startDate
	 * @param endDate
	 * @param orderStatus
	 * @param stationid
	 * @return
	 */
	private List<Record> findAssignNewOrderList(String startDate,String endDate,String orderStatus,Integer stationid){
		if (startDate == null || startDate.equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.equals("")) {
			endDate = DateUtil.getToday();
		}
		if (orderStatus == null) {
			orderStatus = "-1";
		}
		
		if (stationid == null) {
			stationid = -1;
		}
		OrderService service = new OrderServiceImpl();
		List<Record> list=null;
		try {
			list = service.getAssignNewList(startDate, endDate,
					stationid, orderStatus);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 查询配送清单（站点统计）
	 * @param startDate
	 * @param endDate
	 * @param orderStatus
	 * @param stationid
	 * @return
	 */
	private List<Record> findAssignStationOrderList(String startDate,String endDate,String orderStatus,Integer stationid){
		if (startDate == null || startDate.equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.equals("")) {
			endDate = DateUtil.getToday();
		}
		if (orderStatus == null) {
			orderStatus = "-1";
		}
		
		if (stationid == null) {
			stationid = -1;
		}
		OrderService service = new OrderServiceImpl();
		List<Record> list=null;
		try {
			list = service.getAssignStationList(startDate, endDate, stationid, orderStatus);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 进入配送列表页面(站点统计)
	 */
	public void assignstationlistbefore() {
		this.setAttr("today", DateUtil.getToday());
		this.renderJsp("/admin/system/assignstationlist.jsp");
	}
	
	/**
	 * 查询配送清单列表(站点统计)
	 */
	public void assignStationOrderlist() {
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		String orderStatus = this.getPara("orderStatus");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> nList=findAssignStationOrderList(startDate,endDate,orderStatus,stationid);
		this.renderJson(nList);
	}
	
	/**
	 * 导出配送订单报表
	 */
	public void toAssignListExcel() {
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		String orderStatus = this.getPara("orderStatus");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> list=findAssignOrderList(startDate,endDate,orderStatus,stationid);
		String[] header = { "订单编号", "客户姓名", "客户电话", "提货日期","所属站点","站长姓名","站长电话","档口号","菜品名称","件单价","菜品数量","合计金额","订单状态","创建时间" };
		String[] columns={"order_id","cust_name","mobile","take_date","station_name","username","mobile1","position","goods_name","sale_price","sale_num","ammounts","dict_name","order_date"};
		 // 创建工作薄  
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
        // sheet:一张表的简称  
        // row:表里的行  
        // 创建工作薄中的工作表  
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("assignorder"); 
        // 单元格样式：正文内容
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); 
        HSSFFont f=hssfWorkbook.createFont();
        f.setFontName("微软雅黑");
        f.setFontHeightInPoints((short)9);
        cellStyle.setFont(f);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setWrapText(true);//先设置为自动换行
        // 单元格样式：加粗显示
        HSSFCellStyle cellStyleBold = hssfWorkbook.createCellStyle(); 
        HSSFFont bf=hssfWorkbook.createFont();
        bf.setFontName("微软雅黑");
        bf.setFontHeightInPoints((short)9);
        bf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyleBold.setFont(bf);        
        cellStyleBold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyleBold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleBold.setWrapText(true);//先设置为自动换行
        // 创建行  
        HSSFRow row = hssfSheet.createRow(0); 
        row.setHeightInPoints(18);
        // 创建单元格，设置表头 创建列  
        HSSFCell cell = null;  
        //便利表头  
        for (int i = 0; i < header.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header[i]); 
            cell.setCellStyle(cellStyleBold);	// 为单元格添加样式
        } 
     // 设置列宽
        hssfSheet.setColumnWidth(0, 22 * 256); 
        hssfSheet.setColumnWidth(1, 12 * 256);
        hssfSheet.setColumnWidth(2, 13 * 256); 
        hssfSheet.setColumnWidth(3, 12 * 256);
        hssfSheet.setColumnWidth(4, 14 * 256);
        hssfSheet.setColumnWidth(5, 12 * 256);
        hssfSheet.setColumnWidth(6, 13 * 256);
        hssfSheet.setColumnWidth(7, 16 * 256);
        hssfSheet.setColumnWidth(8, 24 * 256);
        hssfSheet.setColumnWidth(9, 12 * 256);
        hssfSheet.setColumnWidth(10, 12 * 256);
        hssfSheet.setColumnWidth(11, 12 * 256);
        hssfSheet.setColumnWidth(12, 12 * 256);
        hssfSheet.setColumnWidth(13, 22 * 256);
        try { 
        	int count=0;
        	boolean isCellTotal=false;
            if(list!=null && list.size()>0){
            	for(Record rec:list){
            		row = hssfSheet.createRow(++count);
            		row.setHeightInPoints(18);
            		isCellTotal=(rec.getStr("cust_name")!=null && rec.getStr("cust_name").equals("合计") || rec.getStr("cust_name").equals("汇总统计"));
            		for (int i = 0; i < header.length; i++) {           			
            			cell=row.createCell(i);
            			if(isCellTotal) cell.setCellStyle(cellStyleBold);
            			else cell.setCellStyle(cellStyle);
            			cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());		
            		} 
            	}
            }
            HttpServletRequest request=this.getRequest();
            HttpServletResponse response=this.getResponse();
            String filename="配送报表"+DateUtil.getDateTime("yyyyMMddHHmmss", new Date())+".xls";
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")); //设置文件头编码方式和文件名   
            OutputStream ouputStream = response.getOutputStream();   
            hssfWorkbook.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }
	}

	/**
	 * 进入到财务订单报表
	 */
	public void financelistbefore() {
		this.setAttr("today", DateUtil.getToday());
		this.renderJsp("/admin/system/financeorderlist.jsp");
	}

	/**
	 * 查询财务订单报表
	 */
	public void financeorderlist() {
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		String orderStatus = this.getPara("orderStatus");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> list=findFinanceOrderList(startDate,endDate,orderStatus,stationid);
		this.renderJson(list);
	}
	
	/**
	 * 查询财务报表清单
	 * @param startDate
	 * @param endDate
	 * @param orderStatus
	 * @param stationid
	 * @return
	 */
	private List<Record> findFinanceOrderList(String startDate,String endDate,String orderStatus,Integer stationid){
		if (startDate == null || startDate.equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.equals("")) {
			endDate = DateUtil.getToday();
		}
		if (orderStatus == null) {
			orderStatus = "-1";
		}
		
		if (stationid == null) {
			stationid = -1;
		}
		OrderService service = new OrderServiceImpl();
		List<Record> list = null;
		try {
			list = service.getFinanceOrderList(startDate, endDate, stationid,
					orderStatus);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	
	/*
	 * 导出财务订单报表
	 */
	public void toFinanceListExcel() {
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		String orderStatus = this.getPara("orderStatus");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> list=findFinanceOrderList(startDate,endDate,orderStatus,stationid);
		String[] header = { "日期", "站点", "需到货时间", "品名","应发货数(件)","单价(元)","金额(元)","实际订单数" };
		String[] columns={"order_date","station","send_time","goods_name","assign_num","sale_price","ammounts","sale_num"};
		 // 创建工作薄  
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
        // sheet:一张表的简称  
        // row:表里的行  
        // 创建工作薄中的工作表  
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("financeorder");
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); 
        HSSFFont f=hssfWorkbook.createFont();
        f.setFontName("微软雅黑");
        f.setFontHeightInPoints((short)9);
        cellStyle.setFont(f);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        
        // 单元格样式：加粗显示
        HSSFCellStyle cellStyleBold = hssfWorkbook.createCellStyle(); 
        HSSFFont bf=hssfWorkbook.createFont();
        bf.setFontName("微软雅黑");
        bf.setFontHeightInPoints((short)9);
        bf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyleBold.setFont(bf);        
        cellStyleBold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyleBold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleBold.setWrapText(true);//先设置为自动换行
        // 创建行  
        HSSFRow row = hssfSheet.createRow(0);  
        row.setHeightInPoints(27);
        // 创建单元格，设置表头 创建列  
        HSSFCell cell = null;  
        //便利表头  
        for (int i = 0; i < header.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header[i]);
            cell.setCellStyle(cellStyleBold);	
        } 
     // 设置列宽
        hssfSheet.setColumnWidth(0, 18 * 256); 
        hssfSheet.setColumnWidth(1, 40 * 256);
        hssfSheet.setColumnWidth(2, 25 * 256); 
        hssfSheet.setColumnWidth(3, 25 * 256);
        hssfSheet.setColumnWidth(4, 15 * 256);
        hssfSheet.setColumnWidth(5, 10 * 256);
        hssfSheet.setColumnWidth(6, 10 * 256);
        hssfSheet.setColumnWidth(7, 12 * 256);
        try { 
        	int count=0;
        	boolean isCellTotal=false;
            if(list!=null && list.size()>0){
            	for(Record rec:list){
            		row = hssfSheet.createRow(++count);
            		row.setHeightInPoints(27);
            		isCellTotal=(rec.getStr("goods_name")!=null && (rec.getStr("goods_name").equals("合计") || rec.getStr("goods_name").equals("汇总统计")));
            		for (int i = 0; i < header.length; i++) {           			
            			cell=row.createCell(i);
            			if(isCellTotal) cell.setCellStyle(cellStyleBold);
            			else cell.setCellStyle(cellStyle);
            			if(i==1 || i==0){
            				String str=null;
            				if(rec.get(columns[i])!=null){
	            				str=rec.get(columns[i]).toString();
	            				str=str.replaceAll("<br/>", "   ");
            				}else{
            					str="";
            				}
            				cell.setCellValue(str);
            			}else if(i>=4){
            				try {
    							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//
    							BigDecimal val=rec.getBigDecimal(columns[i]);
    							if(val==null){
    								cell.setCellValue(0);
    							}else{
    								cell.setCellValue(val.doubleValue());
    							}
    						} catch (Exception e) {
    							// TODO Auto-generated catch block
    							e.printStackTrace();
    						} 
            			}else{
            				cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
            			}			
            		} 
            	}
            }
            // 设置合并单元格
            if(list!=null && list.size()>1){
            	Record r1=list.get(0);
            	int startIndex=1;	// 0+1
            	int endIndex=1;
            	int mstartIndex=1;
            	int mendIndex=1;
            	for(int i=1,size=list.size();i<size;i++){
            		Record r2=list.get(i);
            		String date1=r2.getStr("order_date")==null?"":r2.getStr("order_date");
					String date2=r1.getStr("order_date")==null?"":r1.getStr("order_date");
					if(!date2.equals(date1)){
            			endIndex=i;
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 0, 0));
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 1, 1)); 
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 2, 2));   
            			startIndex=i+1;
					}
            		r1=list.get(i);
            	}
            }
            HttpServletRequest request=this.getRequest();
            HttpServletResponse response=this.getResponse();
//            String filename="order.xls"; 
            String filename="财务订单报表"+DateUtil.getDateTime("yyyyMMddHHmmss", new Date())+".xls";
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")); //设置文件头编码方式和文件名  
            OutputStream ouputStream = response.getOutputStream();   
            hssfWorkbook.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  

        }
	}
	
	/**
	 * 导出配送清单（新版-客户统计）
	 */
	public void exportexcel(){
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		String orderStatus = this.getPara("orderStatus");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> list=findAssignNewOrderList(startDate,endDate,orderStatus,stationid);
		String[] header = { "站点", "站长信息", "客户姓名", "客户电话","档口号","菜品名称","数量(件)","重量(斤)" };
		String[] columns={"station_name","station_master","cust_name","mobile","position","goods_name","sale_nums","sale_weights"};
		 // 创建工作薄  
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
        // sheet:一张表的简称  
        // row:表里的行  
        // 创建工作薄中的工作表  
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("assignlist");
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); 
        HSSFFont f=hssfWorkbook.createFont();
        f.setFontName("微软雅黑");
        f.setFontHeightInPoints((short)9);
        cellStyle.setFont(f);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setWrapText(true);//先设置为自动换行
        
     // 单元格样式：加粗显示
        HSSFCellStyle cellStyleBold = hssfWorkbook.createCellStyle(); 
        HSSFFont bf=hssfWorkbook.createFont();
        bf.setFontName("微软雅黑");
        bf.setFontHeightInPoints((short)9);
        bf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyleBold.setFont(bf);        
        cellStyleBold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyleBold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleBold.setWrapText(true);//先设置为自动换行
        // 用于格式化单元格的数据
//        HSSFDataFormat format = hssfWorkbook.createDataFormat();
        // 创建行  
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeightInPoints(18);  
        // 创建单元格，设置表头 创建列  
        HSSFCell cell = null;  
        //便利表头  
        for (int i = 0; i < header.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header[i]);
            cell.setCellStyle(cellStyleBold);	// 为单元格添加样式
        } 
        // 设置列宽
        hssfSheet.setColumnWidth(0, 20 * 256); 
        hssfSheet.setColumnWidth(1, 20 * 256);
        hssfSheet.setColumnWidth(2, 9 * 256); 
        hssfSheet.setColumnWidth(3, 15 * 256);
        hssfSheet.setColumnWidth(4, 30 * 256);
        hssfSheet.setColumnWidth(5, 25 * 256);
        try { 
        	int count=0;
        	boolean isCellTotal=false;
            if(list!=null && list.size()>0){
            	for(Record rec:list){
            		isCellTotal=(rec.getStr("goods_name")!=null && (rec.getStr("goods_name").equals("客户合计") || rec.getStr("goods_name").equals("站点合计") || rec.getStr("goods_name").equals("汇总统计")));
            		row = hssfSheet.createRow(++count);
            		row.setHeightInPoints(18); 
            		for (int i = 0; i < header.length; i++) {           			
            			cell=row.createCell(i);
            			if(isCellTotal) cell.setCellStyle(cellStyleBold);
            			else cell.setCellStyle(cellStyle);
            			if(i<6){
            				cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());	
            			}else{
            				try {
    							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//
    							BigDecimal val=rec.getBigDecimal(columns[i]);
    							if(val==null){
    								cell.setCellValue(0);
    							}else{
    								cell.setCellValue(val.doubleValue());
    							}
    						} catch (Exception e) {
    							// TODO Auto-generated catch block
    							e.printStackTrace();
    						} 
            			}		
            		} 
            	}
            }
            // 设置合并单元格
            if(list!=null && list.size()>1){
            	Record r1=list.get(0);
            	int startIndex=1;	// 0+1
            	int endIndex=1;
            	int mstartIndex=1;
            	int mendIndex=1;
            	for(int i=1,size=list.size();i<size;i++){
            		Record r2=list.get(i);
            		if(!r2.getStr("station_name").equals(r1.getStr("station_name"))){
            			endIndex=i;
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 0, 0));
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 1, 1));            			
            			startIndex=i+1;
            		}
            		String mobile2=r2.getStr("mobile")==null?"":r2.getStr("mobile");
					String mobile1=r1.getStr("mobile")==null?"":r1.getStr("mobile");
					if(!mobile2.equals(mobile1)){
						mendIndex=i;
						hssfSheet.addMergedRegion(new CellRangeAddress(mstartIndex, mendIndex, 2, 2));
						hssfSheet.addMergedRegion(new CellRangeAddress(mstartIndex, mendIndex, 3, 3));
						hssfSheet.addMergedRegion(new CellRangeAddress(mstartIndex, mendIndex, 4, 4));
						mstartIndex=i+1;
					}
            		r1=list.get(i);
            	}
            }
            
              
            HttpServletRequest request=this.getRequest();
            HttpServletResponse response=this.getResponse();
//            String filename="order.xls"; 
            String filename="配送清单（客户统计）"+DateUtil.getDateTime("yyyyMMddHHmmss", new Date())+".xls";
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")); //设置文件头编码方式和文件名   
            OutputStream ouputStream = response.getOutputStream();   
            hssfWorkbook.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  

        }
	}
	
	/**
	 * 导出配送清单（站点统计）
	 */
	public void exportstationexcel(){
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		String orderStatus = this.getPara("orderStatus");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> list=findAssignStationOrderList(startDate,endDate,orderStatus,stationid);
		if(list!=null && list.size()>0){
			for(Record rec:list){
				if(rec.getStr("station")!=null && rec.getStr("station").trim().length()>0){
					rec.set("station", rec.getStr("station").replaceAll("\r\nnull", ""));
				}
			}
		}
		String[] header = { "站点信息", "货品信息", "货品件数", "备注" };
		String[] columns={"station","goods_name","sale_num","demo"};
		 // 创建工作薄  
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
        // sheet:一张表的简称  
        // row:表里的行  
        // 创建工作薄中的工作表  
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("assignlist");
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); 
        HSSFFont f=hssfWorkbook.createFont();
        f.setFontName("微软雅黑");
        f.setFontHeightInPoints((short)9);
        cellStyle.setFont(f);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setWrapText(true);//先设置为自动换行
        
     // 单元格样式：加粗显示
        HSSFCellStyle cellStyleBold = hssfWorkbook.createCellStyle(); 
        HSSFFont bf=hssfWorkbook.createFont();
        bf.setFontName("微软雅黑");
        bf.setFontHeightInPoints((short)9);
        bf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyleBold.setFont(bf);        
        cellStyleBold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyleBold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleBold.setWrapText(true);//先设置为自动换行
        // 用于格式化单元格的数据
//        HSSFDataFormat format = hssfWorkbook.createDataFormat();
        // 创建行  
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeightInPoints(27);  
        // 创建单元格，设置表头 创建列  
        HSSFCell cell = null;  
        //便利表头  
        for (int i = 0; i < header.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header[i]);
            cell.setCellStyle(cellStyleBold);	// 为单元格添加样式
        } 
        // 设置列宽
        hssfSheet.setColumnWidth(0, 25 * 256); 
        hssfSheet.setColumnWidth(1, 30 * 256);
        hssfSheet.setColumnWidth(2, 9 * 256); 
        hssfSheet.setColumnWidth(3, 40 * 256);
        
        try { 
        	int count=0;
        	boolean isCellTotal=false;
            if(list!=null && list.size()>0){
            	for(Record rec:list){
            		row = hssfSheet.createRow(++count);
            		isCellTotal=(rec.getStr("goods_name")!=null && rec.getStr("goods_name").equals("站点合计") || rec.getStr("goods_name").equals("汇总统计"));
            		row.setHeightInPoints(27); 
            		for (int i = 0; i < header.length; i++) {           			
            			cell=row.createCell(i);
            			if(isCellTotal) cell.setCellStyle(cellStyleBold);
            			else cell.setCellStyle(cellStyle);
            			if(i==0){
            				String str="";
            				if(rec.get(columns[i])!=null){
            					str=rec.get(columns[i]).toString();
	            				str=str.replaceAll("<br/>", "\r\n");
            				}else{
            					str="";
            				}
            				cell.setCellValue(str);	
            			}else if(i==2){
            				try {
    							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//
    							BigDecimal val=rec.getBigDecimal(columns[i]);
    							if(val==null){
    								cell.setCellValue(0);
    							}else{
    								cell.setCellValue(val.doubleValue());
    							}
    						} catch (Exception e) {
    							// TODO Auto-generated catch block
    							e.printStackTrace();
    						} 
            			}else{
            				cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
            			}		
            		}
            	}
            }
            
         // 设置合并单元格
            if(list!=null && list.size()>1){
            	Record r1=list.get(0);
            	int startIndex=1;	// 0+1
            	int endIndex=1;
            	for(int i=1,size=list.size();i<size;i++){
            		Record r2=list.get(i);
            		if(!r2.getStr("station").equals(r1.getStr("station"))){
            			endIndex=i;
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 0, 0));            			
            			startIndex=i+1;
            		}
            		r1=list.get(i);
            	}
            }
              
            HttpServletRequest request=this.getRequest();
            HttpServletResponse response=this.getResponse();
//            String filename="order.xls"; 
            String filename="配送清单（站点统计）"+DateUtil.getDateTime("yyyyMMddHHmmss", new Date())+".xls";
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")); //设置文件头编码方式和文件名   
            OutputStream ouputStream = response.getOutputStream();   
            hssfWorkbook.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  

        }
	}
}
